import pandas as pd
import mysql.connector
from mysql.connector import Error
import json
import csv
from datetime import datetime


class MySQLDataHandler:
    def __init__(self, host, user, password, database):
        self.connection_config = {
            'host': host,
            'user': user,
            'password': password,
            'database': database
        }

    def _get_connection(self):
        try:
            return mysql.connector.connect(**self.connection_config)
        except Error as e:
            print(f"数据库连接错误: {e}")
            return None

    def export_to_csv(self, table_name, output_file):
        """导出数据到CSV文件"""
        conn = self._get_connection()
        if conn:
            try:
                query = f"SELECT * FROM {table_name}"
                df = pd.read_sql(query, conn)
                df.to_csv(output_file, index=False)
                print(f"成功导出数据到 {output_file}")
            except Error as e:
                print(f"导出错误: {e}")
            finally:
                conn.close()

    def export_to_json(self, table_name, output_file):
        """导出数据到JSON文件"""
        conn = self._get_connection()
        if conn:
            try:
                cursor = conn.cursor(dictionary=True)
                cursor.execute(f"SELECT * FROM {table_name}")
                rows = cursor.fetchall()
                with open(output_file, 'w') as f:
                    json.dump(rows, f, indent=4, default=str)
                print(f"成功导出数据到 {output_file}")
            except Error as e:
                print(f"导出错误: {e}")
            finally:
                conn.close()

    def import_from_csv(self, table_name, input_file):
        """从CSV导入数据到数据库"""
        conn = self._get_connection()
        if conn:
            try:
                df = pd.read_csv(input_file)
                self._insert_dataframe(conn, table_name, df)
                print(f"成功从 {input_file} 导入数据")
            except Error as e:
                print(f"导入错误: {e}")
            finally:
                conn.close()

    def import_from_json(self, table_name, input_file):
        """从JSON导入数据到数据库"""
        conn = self._get_connection()
        if conn:
            try:
                with open(input_file) as f:
                    data = json.load(f)
                df = pd.DataFrame(data)
                self._insert_dataframe(conn, table_name, df)
                print(f"成功从 {input_file} 导入数据")
            except Error as e:
                print(f"导入错误: {e}")
            finally:
                conn.close()

    def _insert_dataframe(self, conn, table_name, df):
        """辅助方法：插入DataFrame数据"""
        cursor = conn.cursor()
        try:
            # 获取列名
            columns = ', '.join(df.columns)
            placeholders = ', '.join(['%s'] * len(df.columns))

            # 构建插入语句
            insert_query = f"INSERT INTO {table_name} ({columns}) VALUES ({placeholders})"

            # 转换数据为元组列表
            data = [tuple(x) for x in df.to_numpy()]

            # 执行批量插入
            cursor.executemany(insert_query, data)
            conn.commit()
        except Error as e:
            conn.rollback()
            raise e


# 使用示例
if __name__ == "__main__":
    # 配置数据库连接
    handler = MySQLDataHandler(
        host="localhost",
        user="your_username",
        password="your_password",
        database="your_database"
    )

    # 导出示例
    handler.export_to_csv("employees", "employees_backup.csv")
    handler.export_to_json("products", "products_backup.json")

    # 导入示例
    handler.import_from_csv("employees", "new_employees.csv")
    handler.import_from_json("products", "new_products.json")
